package com.hup.utils.io.poi.excel;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author hugan
 * @date 2019/12/7
 */
public class SheetReaderUtil {

    /**
     * 读取简单的
     */
    public static <T> ArrayList<T> readSimpleSheet(String file, String sheetName, SimpleSheetParser<T> sheetParser) throws IOException {
        if (!(file.endsWith(".xls") || file.endsWith(".xlsx"))) {
            throw new IOException("不支持格式:"+ file);
        }
        try (Workbook wb = file.endsWith(".xls") ? new HSSFWorkbook(new FileInputStream(file)) : new XSSFWorkbook(new FileInputStream(file))) {
            ArrayList<T> res = new ArrayList<>();

            Sheet sheet = wb.getSheet(sheetName);
            int rowCount = sheet.getLastRowNum() + 1;
            for (int i = 0; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                res.add(sheetParser.parseRow(sheet, row));
            }
            return res;
        }
    }

    /**
     * 仅读取columnInfo[]列的数据
     */
    public static <T> SheetData<T> readSheet(Sheet sheet, SheetRowParser<T> parser) {
        ArrayList<SheetColumnInfo> lstColumnInfo = parser.getXlsColumnInfo();
        ArrayList<SheetColumnInfo> lstFoundCol = new ArrayList<>();

        Row row = sheet.getRow(0);
        F1://根据info查找实际对应的列:找到后会转移info到新列表
        for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
            Cell cell = row.getCell(colIndex);
            if (cell == null) continue;
            String colName = cell.getStringCellValue();
            int size = lstColumnInfo.size();
            for (int j = 0; j < size; j++) {
                SheetColumnInfo info = lstColumnInfo.get(j);
                if (info.matchByName(colName, colIndex)) {
                    lstFoundCol.add(info);
                    lstColumnInfo.remove(j);
                    if (lstColumnInfo.isEmpty()) break F1;
                    continue F1;
                }
            }
        }
        if (!lstColumnInfo.isEmpty()) throw new IllegalArgumentException("找不到列:" + lstColumnInfo);

        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

        ArrayList<T> lstData = new ArrayList<>();
        int totalRowIndex = sheet.getLastRowNum();
        for (int r = 1; r <= totalRowIndex; r++) {
            row = sheet.getRow(r);
            //忽略空行
            if(row == null) continue;

            JSONObject jo = new JSONObject();
            for (SheetColumnInfo info : lstFoundCol) {
                Cell cell = row.getCell(info.colIndex);
                String key = info.likeMatcher == null ? info.name : info.likeMatcher.pattern().toString();
                jo.put(key, WbUtil.getCellValue(cell, evaluator));
            }
            int resIndex = r - 1;
            lstData.add(parser.cast(jo, resIndex));
        }
        SheetData<T> res = new SheetData<>();
        res.lstColumnInfo = lstFoundCol;
        res.lstData = lstData;
        return res;
    }

    /**
     * 列信息对象,用于查找列数据
     */
    public static class SheetColumnInfo {
        /**
         * 完整的列名
         */
        private String name;
        /**
         * 被找到后的列index
         */
        private Integer colIndex;
        private Matcher likeMatcher;

        private SheetColumnInfo(){}

        /**
         * @return 根据相等的列名 查找列
         */
        public static SheetColumnInfo equal(String name) {
            SheetColumnInfo info = new SheetColumnInfo();
            info.name = name;
            return info;
        }

        /**
         * @return 根据正则表达式 查找列
         */
        public static SheetColumnInfo like(String regex) {
            SheetColumnInfo info = new SheetColumnInfo();
            info.likeMatcher = Pattern.compile(regex).matcher("");
            return info;
        }

        /**
         * 根据传入的str获取列表中对应的info
         * @param str 可以是具体列表或正则表达式
         */
        public static SheetColumnInfo getByString(List<SheetColumnInfo> list, String str) {
            for (SheetColumnInfo info : list) {
                if (info.likeMatcher == null) {
                    if (info.name.equals(str)) {
                        return info;
                    }
                } else {
                    if (info.likeMatcher.pattern().toString().equals(str)) {
                        return info;
                    }
                }
            }
            return null;
        }

        public String getName() {
            return name;
        }

        @Override
        public String toString() {
            if (likeMatcher == null) {
                return "\"" + name + "\"";
            } else {
                return "[" + likeMatcher.pattern().toString() + "]->\"" + name + "\"";
            }
        }

        /**
         * 匹配正确的表名,如果能匹配,则更新当前对象的name和colIndex
         * @param colName 实际的表名
         * @param colIndex 实际列的index
         * @return 是否匹配成功
         */
        private boolean matchByName(String colName, int colIndex) {
            boolean match;
            if (likeMatcher == null) {
                match = name.equals(colName);
            } else {
                match = likeMatcher.reset(colName).matches();
            }
            if (match) {
                name = colName;
                this.colIndex = colIndex;
            }
            return match;
        }
    }

    public interface SheetRowParser<T> {

        ArrayList<SheetColumnInfo> getXlsColumnInfo();

        /**
         * jo转化成实体对象
         * @param jo key是传入的SheetColumnInfo的name或者regex
         * @param resIndex 这个对象在sheet中的行index,起始于0
         */
        T cast(JSONObject jo, int resIndex);
    }

    public interface SimpleSheetParser<T> {
        T parseRow(Sheet sheet, Row row);
    }

    public static class SheetData<T> {
        public ArrayList<SheetColumnInfo> lstColumnInfo;
        public ArrayList<T> lstData;
    }

}
